<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <input type="button" value="导出" onclick="exportSpecialExcel()"/>
    <script src="./xlxl.js"></script>
    <script>
        let data = [{
            "shopId": "1EKIH9MO6FDDHE7Q2OVA280H4T001PAE",
            "shopName": "乐哈哈",
            "floorName": "L2",
            "industryName": "1",
            "businessId": "2341",
            "acreage": "223.0",
            "acreageRuler": "344.0",
            "totalSales": 14990,
            "dailySales": [
            {
            "theDate": "20201019",
            "shopId": "1EKIH9MO6FDDHE7Q2OVA280H4T001PAE",
            "sales": 60
            },
            {
            "theDate": "20201020",
            "shopId": "1EKIH9MO6FDDHE7Q2OVA280H4T001PAE",
            "sales": 14933
            }
            ]
            },{
            "shopId": "IH9MO6FDDHE7Q2OVA280H4T001PAE",
            "shopName": "虎哈哈",
            "floorName": "L3",
            "industryName": "3",
            "businessId": "55555",
            "acreage": "6666",
            "acreageRuler": "7777",
            "totalSales": 14990,
            "dailySales": [
            {
            "theDate": "20201019",
            "shopId": "IH9MO6FDDHE7Q2OVA280H4T001PAE",
            "sales": 6000
            },
            {
            "theDate": "20201020",
            "shopId": "IH9MO6FDDHE7Q2OVA280H4T001PAE",
            "sales": 456789876
            }
            ]
            }]

        function handleData(data){
            let xlsxData = []
            let cloneData = JSON.parse(JSON.stringify(data))
            var aoa = [
                // ['主要信息', null, null, '其它信息'], // 特别注意合并的地方后面预留2个null
                ['姓名', '性别', '年龄', '注册时间'],
                ['张三', '男', 18, new Date()],
                ['李四', '女', 22, new Date()]
            ];
            //var thead = ['店铺名','楼层','业态','商户编号','面积(m²)','总销售额(元)','20201019','20201020']
            let newData = []
            cloneData.forEach(item => {
                for(let i=0;i<item.dailySales.length;i++){
                    item[item.dailySales[i].theDate] =item.dailySales[i].sales
                }
                newData.push(item)
            })
            var thead = ['店铺名','楼层','业态','商户编号','面积(m²)','面积(尺)','总销售额(元)']
            let theadFlag = ['shopName','floorName','industryName','businessId','acreage','acreageRuler','totalSales']
            for(let i=0;i<data[0].dailySales.length;i++){
                thead.push(data[0].dailySales[i].theDate)
                theadFlag.push(data[0].dailySales[i].theDate)
            }
            xlsxData = [thead]
            for(let j=0;j<newData.length;j++){
                let arr = []
                for(let i=0;i<theadFlag.length;i++){
                    arr[i]= newData[j][theadFlag[i]]
                }
                xlsxData.push(arr)
            }
            
            return xlsxData
        }
        function exportSpecialExcel() {
            var aoa = [
                // ['主要信息', null, null, '其它信息'], // 特别注意合并的地方后面预留2个null
                ['姓名', '性别', '年龄', '注册时间'],
                ['张三', '男', 18, new Date()],
                ['李四', '女', 22, new Date()]
            ];
            let sheetData = handleData(data)
            console.log("DDDDDDDDDDDDDDDD")
            console.log(sheetData)
            //var sheet = XLSX.utils.aoa_to_sheet(aoa);
            var sheet = XLSX.utils.aoa_to_sheet(sheetData);
            // sheet['!merges'] = [
            //     // 设置A1-C1的单元格合并
            //     {s: {r: 0, c: 0}, e: {r: 0, c: 2}}
            // ];
            openDownloadDialog(sheet2blob(sheet), '单元格合并示例.xlsx');
        }
        // 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
	function sheet2blob(sheet, sheetName) {
		sheetName = sheetName || 'sheet1';
		var workbook = {
			SheetNames: [sheetName],
			Sheets: {}
		};
		workbook.Sheets[sheetName] = sheet;
		// 生成excel的配置项
		var wopts = {
			bookType: 'xlsx', // 要生成的文件类型
			bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
			type: 'binary'
		};
		var wbout = XLSX.write(workbook, wopts);
		var blob = new Blob([s2ab(wbout)], {type:"application/octet-stream"});
		// 字符串转ArrayBuffer
		function s2ab(s) {
			var buf = new ArrayBuffer(s.length);
			var view = new Uint8Array(buf);
			for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
			return buf;
		}
		return blob;
    }
    /**
	 * 通用的打开下载对话框方法，没有测试过具体兼容性
	 * @param url 下载地址，也可以是一个blob对象，必选
	 * @param saveName 保存文件名，可选
	 */
	function openDownloadDialog(url, saveName)
	{
		if(typeof url == 'object' && url instanceof Blob)
		{
			url = URL.createObjectURL(url); // 创建blob地址
		}
		var aLink = document.createElement('a');
		aLink.href = url;
		aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
		var event;
		if(window.MouseEvent) event = new MouseEvent('click');
		else
		{
			event = document.createEvent('MouseEvents');
			event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
		}
		aLink.dispatchEvent(event);
	}
    </script>
</body>
</html>